using System;
using Guestbook.domain;
using Guestbook.business;
using System.Data;
using System.Collections.Specialized;
using System.Data.Common;
using System.Configuration;
using System.Text;

namespace Guestbook.dao
{
    public class MySqlDbGuestBookProvider : GuestBookProvider
    {
        DbProviderFactory _dbProvider;
        private string connStringName;
        private string tablePrefix;
        private string parmPrefix;
        private string _connectionString;

        #region - Helpers -

        public DbProviderFactory ProviderFactory
        {
            get { return _dbProvider; }
        }

        protected IDbConnection CreateConnection()
        {
            System.Data.IDbConnection conn = this.ProviderFactory.CreateConnection();
            conn.ConnectionString = _connectionString;

            return conn;
        }

        protected void SetParameters(IDbCommand command, params object[] parameters)
        {
            //set parameter for command
            if (parameters != null)
            {
                for (int i = 0; i < parameters.Length; i++)
                {
                    IDataParameter para = command.CreateParameter();
                    para.Value = parameters[i];
                    para.ParameterName = string.Format("?p{0}", i);

                    command.Parameters.Add(para);
                }
            }
        }

        protected int ExecuteNonQuery(string commandText, params object[] parameters)
        {
            //Get connection object
            IDbConnection conn = CreateConnection();
            try
            {
                //open connection
                conn.Open();

                //Create command object
                IDbCommand cmd = conn.CreateCommand();
                cmd.CommandText = commandText;
                SetParameters(cmd, parameters);

                //execute query
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                    conn.Close();
            }
        }

        protected object ExecuteScalar(string commandText, params object[] parameters)
        {
            //Get connection object
            IDbConnection conn = CreateConnection();
            try
            {
                //open connection
                conn.Open();

                //Create command object
                IDbCommand cmd = conn.CreateCommand();
                cmd.CommandText = commandText;
                SetParameters(cmd, parameters);

                //execute query
                return cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                    conn.Close();
            }
        }

        protected System.Data.IDbDataParameter AddParameterWithValue(
            System.Data.IDbCommand command, string name, object value)
        {
            System.Data.IDbDataParameter para = command.CreateParameter();
            para.ParameterName = name;
            para.Value = value;
            command.Parameters.Add(para);

            return para;
        }

        #endregion

        /// <summary>
        /// Initializes the provider
        /// </summary>
        /// <param name="name">Configuration name</param>
        /// <param name="config">Configuration settings</param>
        public override void Initialize(string name, NameValueCollection config)
        {
            if (config == null)
                throw new ArgumentNullException("config");

            if (String.IsNullOrEmpty(name))
                name = "DbBlogProvider";

            if (String.IsNullOrEmpty(config["description"]))
            {
                config.Remove("description");
                config.Add("description", "Generic Database Blog Provider");
            }

            base.Initialize(name, config);

            if (config["connectionStringName"] == null)
            {
                // default to BlogEngine
                config["connectionStringName"] = "GuestBookEngine";
            }
            connStringName = config["connectionStringName"];
            config.Remove("connectionStringName");

            if (config["tablePrefix"] == null)
            {
                // default
                config["tablePrefix"] = "be_";
            }
            tablePrefix = config["tablePrefix"];
            config.Remove("tablePrefix");

            if (config["parmPrefix"] == null)
            {
                // default
                config["parmPrefix"] = "?";
            }
            parmPrefix = config["parmPrefix"];
            config.Remove("parmPrefix");

            // Throw an exception if unrecognized attributes remain
            if (config.Count > 0)
            {
                string attr = config.GetKey(0);
                if (!String.IsNullOrEmpty(attr))
                    throw new Exception("Unrecognized attribute: " + attr);
            }

            _connectionString = ConfigurationManager.ConnectionStrings[connStringName].ConnectionString;
            string providerName = ConfigurationManager.ConnectionStrings[connStringName].ProviderName;
            _dbProvider = DbProviderFactories.GetFactory(providerName);
        }

        #region - GuestBook -

        public override GuestBook GetGuestBook(int id, int pageSize, int currentPage)
        {
            //Get connection object
            IDbConnection conn = CreateConnection();
            string sqlQuery = "SELECT * FROM v_guestbook WHERE guestbook_id= ?p0";

            try
            {
                //open connection
                conn.Open();

                //Create command object
                IDbCommand cmd = conn.CreateCommand();
                cmd.CommandText = sqlQuery;
                SetParameters(cmd, id);

                GuestBook guestBook = null;
                using (SafeDataReader dr = new SafeDataReader(cmd.ExecuteReader()))
                {
                    guestBook = this.GetGuestBookFromReader(dr);
                }

                if (guestBook != null)
                {
                    int lStart = 0;
                    if (currentPage == 0)
                        currentPage = 1;
                    lStart = (currentPage * pageSize) - pageSize;

                    sqlQuery = "SELECT SQL_CALC_FOUND_ROWS * FROM tblGuestbookMessage ";
                    sqlQuery += "WHERE guestbook_message_guestbook_id = ?p0 ";
                    sqlQuery += "ORDER BY guestbook_message_date_creation DESC ";

                    if (currentPage != -1)
                        sqlQuery += "LIMIT " + lStart.ToString() + "," + pageSize.ToString();

                    sqlQuery += ";";

                    cmd.CommandText = sqlQuery;
                    cmd.Parameters.Clear();
                    SetParameters(cmd, id);

                    using (SafeDataReader dr = new SafeDataReader(cmd.ExecuteReader()))
                    {
                        while (dr.Read())
                            guestBook.Messages.Add(this.GetMessageFromReader(dr));
                    }
                }

                return guestBook;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                    conn.Close();
            }
        }

        public override GuestBook SelectSelfGuestBookByEmail(string email)
        {
            //Get connection object
            IDbConnection conn = CreateConnection();
            string sqlQuery = "SELECT * FROM v_guestbook WHERE guestbook_user_email= ?p0 ";
            sqlQuery += " LIMIT 1";

            try
            {
                //open connection
                conn.Open();

                //Create command object
                IDbCommand cmd = conn.CreateCommand();
                cmd.CommandText = sqlQuery;
                SetParameters(cmd, email);

                GuestBook guestBook = null;
                using (SafeDataReader dr = new SafeDataReader(cmd.ExecuteReader()))
                {
                    guestBook = this.GetGuestBookFromReader(dr);
                }

                return guestBook;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                    conn.Close();
            }
        }

        public override GuestBook SelectSelfGuestBookById(int id)
        {
            //Get connection object
            IDbConnection conn = CreateConnection();
            string sqlQuery = "SELECT * FROM v_guestbook WHERE guestbook_id= ?p0";

            try
            {
                //open connection
                conn.Open();

                //Create command object
                IDbCommand cmd = conn.CreateCommand();
                cmd.CommandText = sqlQuery;
                SetParameters(cmd, id);

                GuestBook guestBook = null;
                using (SafeDataReader dr = new SafeDataReader(cmd.ExecuteReader()))
                {
                    guestBook = this.GetGuestBookFromReader(dr);
                }

                return guestBook;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                    conn.Close();
            }
        }

        public override bool IsExist(int id)
        {
            try
            {
                string sqlQuery = "SELECT COUNT(guestbook_id) ";
                sqlQuery += "FROM v_guestbook WHERE guestbook_id=?p0";

                int result = int.Parse(this.ExecuteScalar(sqlQuery, id).ToString());

                return result > 0;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public override GuestBook SelectGuestBookById(int id)
        {
            //Get connection object
            IDbConnection conn = CreateConnection();
            string sqlQuery = "SELECT * FROM v_guestbook WHERE guestbook_id= ?p0";

            try
            {
                //open connection
                conn.Open();

                //Create command object
                IDbCommand cmd = conn.CreateCommand();
                cmd.CommandText = sqlQuery;
                SetParameters(cmd, id);

                GuestBook guestBook = null;
                using (SafeDataReader dr = new SafeDataReader(cmd.ExecuteReader()))
                {
                    guestBook = this.GetGuestBookFromReader(dr);
                }

                if (guestBook != null)
                {
                    sqlQuery = "SELECT * FROM tblGuestbookMessage WHERE guestbook_message_guestbook_id = ?p0 ";
                    sqlQuery += "ORDER BY guestbook_message_id DESC";
                    cmd.CommandText = sqlQuery;
                    cmd.Parameters.Clear();
                    SetParameters(cmd, id);

                    using (SafeDataReader dr = new SafeDataReader(cmd.ExecuteReader()))
                    {
                        while (dr.Read())
                            guestBook.Messages.Add(this.GetMessageFromReader(dr));
                    }
                }

                return guestBook;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                    conn.Close();
            }
        }

        public override void UpdateGuestBook(GuestBook obj)
        {
            string sqlQuery = "UPDATE tblGuestbook SET ";
            sqlQuery += "guestbook_title=?p0,";
            sqlQuery += "guestbook_active=?p1,";
            sqlQuery += "guestbook_user_password=?p2,";
            sqlQuery += "guestbook_code=?p3,";
            sqlQuery += "guestbook_total_visitors=?p4,";
            sqlQuery += "guestbook_date_creation=?p5,";
            sqlQuery += "guestbook_user_name=?p6,";
            sqlQuery += "guestbook_user_email=?p7,";
            sqlQuery += "guestbook_user_website=?p8 ";
            sqlQuery += "WHERE guestbook_id=?p9";

            int result = this.ExecuteNonQuery(sqlQuery,
                obj.Title,
                obj.Active,
                obj.UserPassword,
                obj.Code,
                obj.TotalVisitors,
                obj.DateCreation,
                obj.UserName,
                obj.UserEmail,
                obj.UserWebsite,
                obj.Id);
        }

        public override int InsertGuestBook(GuestBook obj)
        {
            GuestBook oldObj = SelectSelfGuestBookByEmail(obj.UserEmail);

            if (oldObj != null)
                throw new Exception("Email has register.");

            string sqlQuery = "INSERT tblGuestbook(guestbook_title, guestbook_active, guestbook_user_password, guestbook_code, guestbook_total_visitors, guestbook_date_creation, guestbook_user_name, guestbook_user_email, guestbook_user_website) ";
            sqlQuery += "VALUES(?p0, ?p1, ?p2, ?p3, ?p4, ?p5, ?p6, ?p7, ?p8)";
            sqlQuery += "; SELECT @@Identity AS ID";

            object result = this.ExecuteScalar(sqlQuery,
                obj.Title,
                obj.Active,
                obj.UserPassword,
                obj.Code,
                obj.TotalVisitors,
                obj.DateCreation,
                obj.UserName,
                obj.UserEmail,
                obj.UserWebsite);

            obj.Id = int.Parse(result.ToString());

            return obj.Id;
        }

        protected override GuestBook GetGuestBookFromReader(SafeDataReader dr)
        {
            try
            {
                if (dr.Read())
                {
                    GuestBook obj = new GuestBook();
                    obj.TotalMessages = dr.GetInt32("total_message");
                    obj.LastMessagePosted = dr.GetDateTime("last_post");
                    obj.TotalVisitors = dr.GetInt32("guestbook_total_visitors");
                    obj.DateCreation = dr.GetDateTime("guestbook_date_creation");
                    obj.UserPassword = dr.GetString("guestbook_user_password");
                    obj.Active = dr.GetBoolean("guestbook_active");
                    obj.Title = dr.GetString("guestbook_title");
                    obj.Id = dr.GetInt32("guestbook_id");

                    dr.Close();
                    return obj;
                }

                return null;
            }
            catch
            {
                return null;
            }
        }

        public override void DeleteGuestBook(int id)
        {
            string sqlString = "DELETE FROM tblGuestbook WHERE guestbook_id=?p0";
            this.ExecuteNonQuery(sqlString, id);
        }

        public override void changeGuestBookActiveStatus(int guestBookId, bool status)
        {
            try
            {
                System.Data.IDbCommand command = this.ProviderFactory.CreateCommand();
                StringBuilder query = new StringBuilder();
                query.Append("UPDATE tblGuestbook ");
                query.Append("SET guestbook_active = ?status ");
                query.Append("WHERE guestbook_id = ?id");
                command.CommandText = query.ToString();

                AddParameterWithValue(command, "?id", guestBookId);
                AddParameterWithValue(command, "?status", status);

                int rowsAffected = command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region - Message -

        public override Message SelectMessagesById(int id)
        {
            //Get connection object
            IDbConnection conn = CreateConnection();
            string sqlQuery = "SELECT * FROM tblGuestbookMessage ";
            sqlQuery += "WHERE guestbook_message_id= ?p0";

            try
            {
                //open connection
                conn.Open();

                //Create command object
                IDbCommand cmd = conn.CreateCommand();
                cmd.CommandText = sqlQuery;
                SetParameters(cmd, id);

                Message obj = null;
                using (SafeDataReader dr = new SafeDataReader(cmd.ExecuteReader()))
                {
                    obj = this.GetMessageFromReader(dr);
                }

                return obj;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                    conn.Close();
            }
        }

        public override void UpdateMessage(Message obj)
        {
            string sqlQuery = "UPDATE tblGuestbookMessage SET ";
            sqlQuery += "guestbook_message_user_name=?p0,";
            sqlQuery += "guestbook_message_user_email=?p1,";
            sqlQuery += "guestbook_message_user_website=?p2,";
            sqlQuery += "guestbook_message_user_message=?p3,";
            sqlQuery += "guestbook_message_date_creation=?p4,";
            sqlQuery += "guestbook_message_active=?p5,";
            sqlQuery += "guestbook_message_guestbook_id=?p6 ";
            sqlQuery += "WHERE guestbook_message_id=?p7";

            int result = this.ExecuteNonQuery(sqlQuery,
                obj.Author,
                obj.UserEmail,
                obj.UserWebsite,
                obj.Content,
                obj.DateCreation,
                obj.Active,
                obj.ParentId,
                obj.Id);
        }

        public override int InsertMessage(Message obj)
        {
            try
            {
                IDbConnection conn = CreateConnection();
                //open connection
                conn.Open();
                IDbTransaction trans = conn.BeginTransaction();

                try
                {
                    string sqlQuery = "INSERT tblGuestbookMessage(guestbook_message_user_name, guestbook_message_user_email, guestbook_message_user_website, guestbook_message_user_message, guestbook_message_date_creation, guestbook_message_active, guestbook_message_guestbook_id) ";
                    sqlQuery += "VALUES(?p0, ?p1, ?p2, ?p3, ?p4, ?p5, ?p6)";
                    sqlQuery += "; SELECT @@Identity AS ID";

                    //Create command object
                    IDbCommand cmd = conn.CreateCommand();
                    cmd.CommandText = sqlQuery;
                    SetParameters(cmd, obj.Author,
                        obj.UserEmail,
                        obj.UserWebsite,
                        obj.Content,
                        obj.DateCreation,
                        obj.Active,
                        obj.ParentId);

                    //execute query
                    object result = cmd.ExecuteScalar();
                    obj.Id = int.Parse(result.ToString());

                    /*
                    sqlQuery = "UPDATE tblGuestbook SET ";
                    sqlQuery += "guestbook_total_messages = guestbook_total_messages + 1, ";
                    sqlQuery += "guestbook_last_message_posted = now() ";
                    sqlQuery += "WHERE guestbook_id = ?p0";

                    cmd.Parameters.Clear();
                    cmd.CommandText = sqlQuery;
                    SetParameters(cmd, obj.ParentId);
                    cmd.ExecuteNonQuery();
                    */
                    trans.Commit();
                }
                catch (Exception)
                {
                    trans.Rollback();
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }

                return obj.Id;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public override void DeleteMessagesById(int id)
        {
            string sqlString = "DELETE FROM tblGuestbookMessage ";
            sqlString += "WHERE guestbook_message_id=?p0";

            this.ExecuteNonQuery(sqlString, id);
        }

        protected override Message GetMessageFromReader(SafeDataReader dr)
        {
            try
            {
                Message message = new Message();
                message.Number = dr.GetInt32("guestbook_message_number");
                message.ParentId = dr.GetInt32("guestbook_message_guestbook_id");
                message.Active = dr.GetBoolean("guestbook_message_active");
                message.DateCreation = dr.GetDateTime("guestbook_message_date_creation");
                message.Content = dr.GetString("guestbook_message_user_message");
                message.UserWebsite = dr.GetString("guestbook_message_user_website");
                message.UserEmail = dr.GetString("guestbook_message_user_email");
                message.Author = dr.GetString("guestbook_message_user_name");
                message.Id = dr.GetInt32("guestbook_message_id");

                return message;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion
    }
}
